package ihuotui.test.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

import javax.sql.DataSource;

import org.apache.commons.lang3.RandomUtils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mysql.cj.api.log.Log;

public class ConnectionUtils {
	static volatile DataSource ds;

	public static void main(String[] args) {

		Connection connection = ConnectionUtils.getConnection();
		System.out.println("insert");
		testOneInsert(10000l);
		testOneInsert(10 * 10000l);
		testOneInsert(100 * 10000l);
		System.out.println("query");
		testQuery(10000l);
		testQuery(10 * 10000l);
		testQuery(100 * 10000l);
		/**
		 * 千万级别 name 非唯一索引 btree insert num:10000 start:Sat Feb 11 23:17:34 CST
		 * 2017 num:10000 end:Sat Feb 11 23:17:40 CST 2017 num:10000 total:6488
		 * ecah:0.648 num:100000 start:Sat Feb 11 23:17:40 CST 2017 num:100000
		 * end:Sat Feb 11 23:18:27 CST 2017 num:100000 total:46919
		 * ecah:0.46900000000000003 num:1000000 start:Sat Feb 11 23:18:27 CST
		 * 2017 num:1000000 end:Sat Feb 11 23:26:48 CST 2017 num:1000000
		 * total:500536 ecah:0.5 query num:10000 start:Sat Feb 11 23:26:48 CST
		 * 2017 num:10000 end:Sat Feb 11 23:26:57 CST 2017 num:10000 total:9198
		 * ecah:0.919 num:10000 error:0 num:100000 start:Sat Feb 11 23:26:57 CST
		 * 2017 num:100000 end:Sat Feb 11 23:27:54 CST 2017 num:100000
		 * total:56601 ecah:0.5660000000000001 num:100000 error:0 num:1000000
		 * start:Sat Feb 11 23:27:54 CST 2017 num:1000000 end:Sat Feb 11
		 * 23:35:17 CST 2017 num:1000000 total:443672 ecah:0.443 num:1000000
		 * error:0
		 * 
		 * 百万及
		 * 
		 * insert num:10000 start:Sun Feb 12 00:03:49 CST 2017 num:10000 end:Sun
		 * Feb 12 00:03:55 CST 2017 num:10000 total:6314 ecah:0.631 num:100000
		 * start:Sun Feb 12 00:03:55 CST 2017 num:100000 end:Sun Feb 12 00:04:42
		 * CST 2017 num:100000 total:46206 ecah:0.462 num:1000000 start:Sun Feb
		 * 12 00:04:42 CST 2017 num:1000000 end:Sun Feb 12 00:11:49 CST 2017
		 * num:1000000 total:426957 ecah:0.426 query num:10000 start:Sun Feb 12
		 * 00:11:49 CST 2017 num:10000 end:Sun Feb 12 00:11:51 CST 2017
		 * num:10000 total:2706 ecah:0.27 num:10000 error:0 num:100000 start:Sun
		 * Feb 12 00:11:51 CST 2017 num:100000 end:Sun Feb 12 00:12:15 CST 2017
		 * num:100000 total:23183 ecah:0.231 num:100000 error:0 num:1000000
		 * start:Sun Feb 12 00:12:15 CST 2017 num:1000000 end:Sun Feb 12
		 * 00:15:57 CST 2017 num:1000000 total:222473 ecah:0.222 num:1000000
		 * error:0
		 * 
		 */
	}

	private static void testBatInsert() {
		Long start = new Date().getTime();
		System.out.println("start:" + new Date());
		// insert
		for (int i = 0; i < 1000; i++) {
			insert(0l + i * 10000, (i + 1) * 10000l);
		}
		System.out.println("end:" + new Date());
		Long end = new Date().getTime();
		System.out.println("total:" + (end - start));
		// 33693 一千万 无索引
	}

	private static void testOneInsert(Long num) {
		Long start = new Date().getTime();
		System.out.println("num:" + num + " " + "start:" + new Date());
		// insert
		Long max = num;
		for (int i = 0; i < max; i++) {
			insert(0l + i, 0l + i);
		}
		System.out.println("num:" + num + " " + "end:" + new Date());
		Long end = new Date().getTime();
		System.out.println(
				"num:" + num + " " + "total:" + (end - start) + " ecah:" + (end - start) / (max / 1000) * 0.001);
		/**
		 * 
		
		 */
	}

	private static void testQuery(Long num) {
		Long start = new Date().getTime();
		System.out.println("num:" + num + " " + "start:" + new Date());
		Long max = num;
		Long error = 0l;
		for (Long i = 1l; i < max; i++) {
			List<User> list = getData(RandomUtils.nextLong(1, 100 * 10000 - 1) + "");
			if (list != null) {
				// System.out.println(list);
			}
			if (list == null) {
				error++;
			}
		}
		System.out.println("num:" + num + " " + "end:" + new Date());
		Long end = new Date().getTime();
		System.out.println(
				"num:" + num + " " + "total:" + (end - start) + " ecah:" + (end - start) / (max / 1000) * 0.001);
		System.out.println("num:" + num + " " + "error:" + error);
	}

	public static void insert(Long start, Long end) {
		Connection connection = ConnectionUtils.getConnection();
		// INSERT INTO MyTable(ID,NAME) VALUES(7,'003'),(8,'004'),(9,'005');
		String sql = "INSERT INTO user(name) VALUES ";
		StringBuilder values = new StringBuilder();
		if (start.longValue() == end.longValue()) {
			values.append("('" + start + "')");
		} else {
			for (Long i = start; i < end; i++) {
				values.append("('" + i + "')");
				if (i != end - 1) {
					values.append(",");
				}
			}
		}
		sql = sql + values.toString() + " ;";
		PreparedStatement pStatement = null;
		try {
			pStatement = (PreparedStatement) connection.prepareStatement(sql);
			pStatement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pStatement != null) {
					pStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static List<User> getData(String name) {
		List<User> list = new ArrayList<User>(1);
		Connection connection = ConnectionUtils.getConnection();
		String sql = "SELECT name,age,id FROM user where name='" + name + "'";
		PreparedStatement pStatement = null;
		ResultSet rSet = null;
		try {
			pStatement = (PreparedStatement) connection.prepareStatement(sql);
			rSet = pStatement.executeQuery();
			while (rSet.next()) {
				User user = new User(rSet.getString("name"), rSet.getInt("age"), rSet.getLong("id"));
				list.add(user);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rSet != null) {
					rSet.close();
				}
				if (pStatement != null) {
					pStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public static Connection getConnection() {
		if (ds == null) {
			newCon();
		}
		try {
			Connection connection = ds.getConnection();
			return connection;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;

		/*
		 * String driver = "com.mysql.jdbc.Driver"; String url =
		 * "jdbc:mysql://localhost:3306/mysql"; String user = "root"; String
		 * password = ""; try { Class.forName("com.mysql.jdbc.Driver"); } catch
		 * (ClassNotFoundException e) { // TODO Auto-generated catch block
		 * e.printStackTrace(); } Connection connection = null; try { connection
		 * = DriverManager.getConnection(url, user, password); } catch
		 * (SQLException e) { e.printStackTrace(); } return connection;
		 */

	}

	private static synchronized void newCon() {
		ds = new ComboPooledDataSource("mysqlConnection");
	}
}
